//
//  VHLSQLiteObject+SQL.swift
//  VHLSqlite
//
//  Created by Vincent on 2021/6/7.
//

import Foundation

// MARK: - 表操作 SQL
extension VHLSQLiteObject {
    // MARK: 创建表的 sql 语句
    func createTableSQL() -> String {
        if tableName.isEmpty { return "" }
        
        var sql = "CREATE TABLE IF NOT EXISTS " + tableName
        
        let properties = getProperties()
        
        //拼接动态字段
        var columsStr = ""
        for (index, property) in properties.enumerated() {
            if property.key == primaryKey() { continue }
            
            columsStr += "'\(property.key)' \(property.sqlType)"
            
            if index < properties.count - 1 {
                columsStr += ","
            }
        }

        //添加主键
        sql += "(" + primaryKey() + " INTEGER UNIQUE PRIMARY KEY NOT NULL,"
        sql += columsStr
        sql += ")"
        
        return sql
    }
    // MARK: 获取表信息的 sql 语句
    func tableInfoSQL() -> String {
        if tableName.isEmpty { return "" }
        
        // SELECT * FROM pragma_table_info(tableName)
        // PRAGMA table_info(tableName)
        return "PRAGMA table_info('\(tableName)')"
    }
    
    // MARK: 添加列的 sql
    func addCloumnSQL(_ columnName: String, _ columnType: String) -> String {
        if tableName.isEmpty { return "" }
        
        return "ALTER TABLE " + tableName + " ADD COLUMN '\(columnName)' \(columnType)"
    }
}

// MARK: - 查询 SQL
extension VHLSQLiteObject {
    // MARK: 查询自己的条件 SQL，不带 WHERE
    func whereSelfSQL() -> String {
        var whereStr: String = ""
        
        let properties = getProperties()

        if let primary = properties.first(where: { $0.isPrimaryKey }) {
            if let value = primary.value, !VHLSQLiteTool.valueIsNil(value) {
                whereStr = "`\(primary.key)` = '\(value)'"
            }
        }

        if let pk = pkid {                  // 主键
            whereStr = "pkid = '\(pk)'"
        } else if let uKeys = uniqueKeys(), uKeys.count > 0 {   // uniqueKey
            properties.filter({ uKeys.contains($0.key )}).forEach { (property) in
                let key = property.key
                let value = property.sqlValue
                
                if let value = value, !VHLSQLiteTool.valueIsNil(value) {
                    if whereStr.isEmpty {
                        whereStr += "`\(key)` = '\(value)'"
                    } else {
                        whereStr += " AND `\(key)` = '\(value)'"
                    }
                }
            }
        } else {
            for propertie in properties {
                let key = propertie.key
                let value = propertie.sqlValue
                
                if let value = value, !VHLSQLiteTool.valueIsNil(value) {
                    if whereStr.isEmpty {
                        whereStr += "`\(key)` = '\(value)'"
                    } else {
                        whereStr += " AND `\(key)` = '\(value)'"
                    }
                }
            }
        }
        
        return whereStr
    }
    
    // MARK: 查询该对象的条件 SQL (带参数)，不带 WHERE
    func whereSelfSQLParams() -> (whereSQL: String, params: [Any]) {
        var whereSQL = ""
        var params: [Any] = []
        
        let properties = getProperties()

        if let pk = pkid {                  // 主键
            whereSQL = "pkid = ?"
            params.append(pk as Any)
        } else if let uKeys = uniqueKeys(), uKeys.count > 0 {   // uniqueKey
            properties.filter({ uKeys.contains($0.key )}).forEach { (property) in
                let key = property.key
                let value = property.sqlValue
                
                if let value = value, !VHLSQLiteTool.valueIsNil(value) {
                    if whereSQL.isEmpty {
                        whereSQL += "`\(key)` = ?"
                    } else {
                        whereSQL += " AND `\(key)` = ?"
                    }
                    params.append(value as Any)
                }
            }
        } else {
            for property in properties {
                let key = property.key
                let value = property.sqlValue
                
                if let value = value, !VHLSQLiteTool.valueIsNil(value) {
                    if whereSQL.isEmpty {
                        whereSQL += "`\(key)` = ?"
                    } else {
                        whereSQL += " AND `\(key)` = ?"
                    }
                    params.append(value as Any)
                }
            }
        }
        
        return (whereSQL, params)
    }
    
    // MARK: 查询该对象的 SQL
    func selectSelfSQL() -> String {
        if tableName.isEmpty { return "" }

        let whereSQL = whereSelfSQL()
        let sql = "SELECT * FROM \(tableName) WHERE \(whereSQL)"

        return sql
    }
}

// MAKR: - 保存或修改的 SQL
extension VHLSQLiteObject {
    // MARK: 带参数的插入或修改 SQL
    func saveOrUpdateSQLParams(isInsert: Bool = true) -> (sql: String, params: [Any]) {
        var sql = ""
        var params: [Any] = []
        
        if isInsert {
            sql = "INSERT INTO \(tableName)("
        } else {
            sql = "UPDATE \(tableName) SET "
        }
        
        var valuesStr = ""
        var isFirst = true
        
        let properties = getProperties()
        for property in properties {
            let key = property.key
            let value = property.sqlValue
            if value == nil { continue }
            
            if property.isPrimaryKey { continue }
            
            if isInsert {
                sql += isFirst ? "`\(key)`" : ", `\(key)`"
                valuesStr += isFirst ? " VALUES (?" : ", ?"
            } else {
                sql += isFirst ? "`\(key)` = ?" : ", `\(key)` = ?"
            }
            params.append(value as Any)
            isFirst = false
        }
        
        //
        if isInsert {
            sql += ")" + valuesStr + ")"
        } else { // 查询修改的 where 语句
            let whereSQLParams = whereSelfSQLParams()
            if !whereSQLParams.whereSQL.isEmpty {
                sql += " WHERE \(whereSQLParams.whereSQL)"
                params.append(contentsOf: whereSQLParams.params)
            }
        }
        
        return (sql, params)
    }
    
    // MARK: 插入或修改的 SQL
    func saveOrUpdateSQL(isInsert: Bool = true) -> String {
        var sql = ""
        
        if isInsert {
            sql = "INSERT INTO \(tableName)("
        } else {
            sql = "UPDATE \(tableName) SET "
        }
        
        var valuesStr = ""
        var first = true
        
        let properties = getProperties()
        for property in properties {
            let key = property.key
            guard let value = property.sqlValue else {
                continue
            }
            if VHLSQLiteTool.valueIsNil(value) { continue }
            
            if isInsert {
                sql += first ? "`\(key)`" : ", `\(key)`"
                valuesStr += first ? " VALUES ('\(value)'" : ", '\(value)'"
            } else {
                sql += first ? "`\(key)` = '\(value)'" : ", `\(key)` = '\(value)'"
            }
            first = false
        }
        
        if isInsert {
            sql += ")" + valuesStr + ")"
        } else {
            let whereSQL = whereSelfSQL()
            if !whereSQL.isEmpty {
                sql += (whereSQL.contains("WHERE") ? " " : " WHERE ") + whereSQL
            }
        }
        
        return sql
    }
}

// MARK: - 单个修改
extension VHLSQLiteObject {
    func updateSQL(parameters: [String: Any], whereSQL: String) -> String {
        let objectProperties = getProperties()
        let objectPropertyKeys = objectProperties.map({ $0.key })
        
        // 预筛选对象包含的属性，不存在的属性不处理
        let parames = parameters.filter({ objectPropertyKeys.contains($0.key) })
        if parames.count <= 0 { return "" }
        
        var sql = "UPDATE \(tableName) SET "
        
        var isFirst = true
        
        for param in parames {
            let key = param.key
            let value = param.value
            
            if VHLSQLiteTool.valueIsNil(value) { continue }
            
            sql += isFirst ? "`\(key)` = '\(value)'" : ", `\(key)` = '\(value)'"
            isFirst = false
        }
        
        // WHERE
        if !whereSQL.isEmpty {
            sql += (whereSQL.contains("WHERE") ? " " : " WHERE ") + whereSQL
        }

        return sql
    }
    
    func updateSQLPrames(parameters: [String: Any], whereSQL: String) -> (sql: String, params: [Any]) {
        let objectProperties = getProperties()
        let objectPropertyKeys = objectProperties.map({ $0.key })
        
        // 预筛选对象包含的属性，不存在的属性不处理
        let parameters = parameters.filter({ objectPropertyKeys.contains($0.key) })
        if parameters.count <= 0 { return ("", []) }
        
        var sql = "UPDATE \(tableName) SET "
        var params: [Any] = []

        var isFirst = true
        
        for parameter in parameters {
            let key = parameter.key
            let value = parameter.value
            
            if VHLSQLiteTool.valueIsNil(value) { continue }
            
            sql += isFirst ? "`\(key)` = ?" : ", `\(key)` = ?"
            params.append(value as Any)

            isFirst = false
        }
        
        // WHERE
        if !whereSQL.isEmpty {
            sql += (whereSQL.contains("WHERE") ? " " : " WHERE ") + whereSQL
        }

        return (sql, params)
    }
}
